﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Data.SqlClient;

namespace Db
{
    public class EventDb : AbstractDb
    {
        MovieDb moviedb = new MovieDb();
        CinemaDb cinemadb = new CinemaDb();

        public Event GetEventByID(int id)
        {
            Event even = null;
            SqlDataReader q = this.db.Query("SELECT * FROM Bif_Event WHERE event_ID = " + id);
            if (q.HasRows && q.Read())
            {
                int uID = q.GetInt32(q.GetOrdinal("event_ID"));
                DateTime time = q.GetDateTime(q.GetOrdinal("event_time"));
                double price = q.GetDouble(q.GetOrdinal("event_price"));
                int seatsBooked = q.GetInt32(q.GetOrdinal("event_seatsBooked"));
                int seatsSold = q.GetInt32(q.GetOrdinal("event_seatsSold"));
                Movie newMovie = moviedb.GetMovieByID(q.GetInt32(q.GetOrdinal("event_movieID")));
                Cinema newCinema = cinemadb.GetCinemaByID(q.GetInt32(q.GetOrdinal("event_cinemaID")));

                even = new Event(uID, time, newMovie, newCinema, price, seatsBooked,seatsSold);
            }
            return even;
        }

        public List<Event> GetAllEvents()
        {
            List<Event> events = new List<Event>();
            SqlDataReader q = this.db.Query("SELECT * FROM Bif_Event");
            if (q.HasRows && q.Read())
            {
                while (q.Read())
                {
                    int uID = q.GetInt32(q.GetOrdinal("event_ID"));
                    DateTime time = q.GetDateTime(q.GetOrdinal("event_time"));
                    double price = q.GetDouble(q.GetOrdinal("event_price"));
                    int seatsBooked = q.GetInt32(q.GetOrdinal("event_seatsBooked"));
                    int seatsSold = q.GetInt32(q.GetOrdinal("event_seatsSold"));
                    Movie newMovie = moviedb.GetMovieByID(q.GetInt32(q.GetOrdinal("event_movieID")));
                    Cinema newCinema = cinemadb.GetCinemaByID(q.GetInt32(q.GetOrdinal("event_cinemaID")));

                    Event even = new Event(uID, time, newMovie, newCinema, price, seatsBooked, seatsSold);
                    events.Add(even);
                }
            }
            return events;
        }

        public List<Event> GetAllActiveEventsByCinemaID(int cinemaID)
        {
            List<Event> events = new List<Event>();
            Statement stmt = new Statement("SELECT * FROM Bif_Event WHERE event_time > @today AND event_cinemaID =" + cinemaID);
            stmt.AddParameter("@today", DateTime.Today, "datetime");

            SqlDataReader q = this.db.Query(stmt);

            if (q.HasRows)
            {
                while (q.Read())
                {
                    int uID = q.GetInt32(q.GetOrdinal("event_ID"));
                    DateTime time = q.GetDateTime(q.GetOrdinal("event_time"));
                    double price = q.GetDouble(q.GetOrdinal("event_price"));
                    int seatsBooked = q.GetInt32(q.GetOrdinal("event_seatsBooked"));
                    int seatsSold = q.GetInt32(q.GetOrdinal("event_seatsSold"));
                    Movie newMovie = moviedb.GetMovieByID(q.GetInt32(q.GetOrdinal("event_movieID")));
                    Cinema newCinema = cinemadb.GetCinemaByID(q.GetInt32(q.GetOrdinal("event_cinemaID")));

                    Event even = new Event(uID, time, newMovie, newCinema, price, seatsBooked, seatsSold);
                    events.Add(even);
                }
            }
            return events;
        }

        public void InsertEvent(Event even)
        {
            Statement stmt = new Statement("INSERT INTO Bif_Event VALUES event_movieID = @movie, event_cinemaID = @cinema, event_time = @eventTime, event_price = @price, event_seatsBooked = @seatsBooked, event_seatsSold = @seatsSold");

            stmt.AddParameter("@movie", even.Movie.ID, "int");
            stmt.AddParameter("@cinema", even.Cinema.ID, "int");
            stmt.AddParameter("@eventTime", even.Time, "datetime");
            stmt.AddParameter("@price", even.Price, "int");
            stmt.AddParameter("@seatsBooked", even.SeatsBooked, "int");
            stmt.AddParameter("seatsSold", even.SeatsSold, "int");

            this.db.QueryInsert(stmt);
        }

        public void UpdateEvent(Event even, int id)
        {
            Statement stmt = new Statement("UPDATE Bif_Event SET event_movieID = @movie, event_cinemaID = @cinema, event_time = @eventTime, event_price = @price, event_seatsBooked = @seatsBooked, event_seatsSold = @seatsSold WHERE event_ID = @id");
            stmt.AddParameter("@id", id, "int");
            stmt.AddParameter("@movie", even.Movie.ID, "int");
            stmt.AddParameter("@cinema", even.Cinema.ID, "int");
            stmt.AddParameter("@eventTime", even.Time, "datetime");
            stmt.AddParameter("@price", even.Price, "int");
            stmt.AddParameter("@seatsBooked", even.SeatsBooked, "int");
            stmt.AddParameter("seatsSold", even.SeatsSold, "int");
            this.db.QueryInsert(stmt);
        }

        public void DeleteEvent(int id)
        {
            this.db.QueryDelete("DELETE FROM Bif_Event WHERE event_ID =" + id);
        }
    }
}
